<?php
/**
 * Groo storage implementation for PHP's PEAR database abstraction
 * layer.
 *
 * Required parameters:<pre>
 *      'phptype'       The database type (e.g. 'pgsql', 'mysql', etc.).
 *      'charset'       The database's internal charset.</pre>
 *
 * Optional values:<pre>
 *      'table'         The name of the items table in 'database'. Defaults
 *                      to 'groo_items'</pre>
 *
 * Required by some database implementations:<pre>
 *      'hostspec'      The hostname of the database server.
 *      'protocol'      The communication protocol ('tcp', 'unix', etc.).
 *      'database'      The name of the database.
 *      'username'      The username with which to connect to the database.
 *      'password'      The password associated with 'username'.
 *      'options'       Additional options to pass to the database.
 *      'tty'           The TTY on which to connect to the database.
 *      'port'          The port on which to connect to the database.</pre>
 *
 * The table structure is defined in scripts/drivers/groo_items.sql.
 *
 * $Horde: groo/lib/Driver/sql.php,v 1.28.2.12 2006/01/18 19:47:27 ben Exp $
 *
 * Copyright 2001-2006 Horde Project (http://www.horde.org/)
 *
 * See the enclosed file LICENSE for license information (ASL). If you
 * did not receive this file, see http://www.horde.org/licenses/asl.php.
 *
 * @author  Chuck Hagenbuch <chuck@horde.org>
 * @author  Luciano Ramalho <luciano@ramalho.org>
 * @since   Groo 1.0
 * @package Groo
 */

require_once(GROO_BASE . '/lib/Book.php');

class Groo_Driver_sql extends Groo_Driver {

    /**
     * Hash containing connection parameters.
     *
     * @var array
     */
    var $_params = array();

    /**
     * Handle for the current database connection.
     *
     * @var DB
     */
    var $_db;

    /**
     * Are we connected to the SQL server?
     *
     * @var boolean
     */
    var $_connected = false;

    /**
     * Construct a new SQL storage object.
     *
     * @param string $collection   The name of the collection to load/save items from.
     * @param array  $params    A hash containing connection parameters.
     */
    function Groo_Driver_sql($collection, $params = array())
    {
        $this->_collection = $collection;
        $this->_params = $params;
    }

    /**
     * Retrieve one item from the database.
     *
     * @param string $itemId  The ID of the item to retrieve.
     *
     * @return array  The array of item attributes.
     */
    function get($itemId)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        /* Build the SQL query. */
        $query = 'SELECT * FROM ' . $this->_params['table_prefix'].'items' .
                 ' WHERE item_owner = ? AND item_id = ?';
        $values = array($this->_collection, $itemId);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::get(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Execute the query. */
        $result = $this->_db->query($query, $values);

        if (is_a($result, 'PEAR_Error')) {
            return $result;
        }

        $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
        if (is_a($row, 'PEAR_Error')) {
            return $row;
        }

        return $this->_buildItem($row);
    }
    

    /**
     * Retrieve one item from the database by UID.
     *
     * @param string $uid  The UID of the item to retrieve.
     *
     * @return array  The array of item attributes.
     */
    function getByUID($uid)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        /* Build the SQL query. */
        $query = 'SELECT * FROM ' . $this->_params['table_prefix'].'items' .
                 ' WHERE item_uid = ?';
        $values = array($uid);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::getByUID(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Execute the query. */
        $result = $this->_db->query($query, $values);
        if (is_a($result, 'PEAR_Error')) {
            return $result;
        }

        $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
        if (is_a($row, 'PEAR_Error')) {
            return $row;
        }

        /* Decode and return the task. */
        $this->_collection = $row['item_owner'];
        return $this->_buildItem($row);
    }
    
    /**
     * Get the description (short summary) of a item.
     *
     * @param integer $item_id  The item to describe.
     */
    function getItemDescription($itemId)
    {
        $item = $this->get($itemId);
        if (is_a($item, 'PEAR_Error')) {
            return $item;
        }
        return $item['desc'].':('.$item['item_owner'].','.$item['item_id'].')';    
        
    }


    /**
     * Add a item to the backend storage.
     *
     * @param string $desc      The first line of the item.
     * @param string $body      The whole item body.
     * @param string $category  The category of the item.
     * @param string $uid       A Unique Identifier for the item.
     *
     * @return string  The unique ID of the new item.
     */
    function addItem($desc, $bookId, $body='', $category = '', $uid = null)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        $itemId = md5(uniqid(mt_rand(), true));
        if (is_null($uid)) {
            $uid = $this->generateUID();
        }

        $query = 'INSERT INTO ' . $this->_params['table_prefix'].'items' .
                 ' (item_owner, item_id, item_desc, item_body, item_book_id, item_category, item_uid)' .
                 ' VALUES (?, ?, ?, ?, ?, ?, ?)';
        $values = array($this->_collection,
                        $itemId,
                        String::convertCharset($desc, NLS::getCharset(), $this->_params['charset']),
                        String::convertCharset($body, NLS::getCharset(), $this->_params['charset']),
                        $bookId,
                        String::convertCharset($category, NLS::getCharset(), $this->_params['charset']),
                        String::convertCharset($uid, NLS::getCharset(), $this->_params['charset']));

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::add(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Attempt the insertion query. */
        $result = $this->_db->query($query, $values);

        /* Return an error immediately if the query failed. */
        if (is_a($result, 'PEAR_Error')) {
            Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
            return $result;
        }

        /* Log the creation of this item in the history log. */
        $history = &Horde_History::singleton();
        $history->log('groo:' . $this->_collection . ':' . $uid, array('action' => 'add'), true);

        return $itemId;
    }


    /**
     * Modify an existing item.
     *
     * @param string $itemId    The item to modify.
     * @param string $desc      The description (long) of the item.
     * @param string $body      The description (long) of the item.
     * @param string $category  The category of the item.
     */
    function modify($itemId, $body, $category = null)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        $query  = 'UPDATE ' . $this->_params['table_prefix'].'items' .
                  ' SET item_desc = ?, item_body = ?';
        $values = array(String::convertCharset($desc, NLS::getCharset(), $this->_params['charset']),
                        String::convertCharset($body, NLS::getCharset(), $this->_params['charset']));

        // Don't change the category if it isn't provided.
        if (!is_null($category)) {
            $query .= ', item_category = ?';
            $values[] = String::convertCharset($category, NLS::getCharset(), $this->_params['charset']);
        }
        $query .= ' WHERE item_owner = ? AND item_id = ?';
        array_push($values, $this->_collection, $itemId);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::modify(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Attempt the update query. */
        $result = $this->_db->query($query, $values);

        if (is_a($result, 'PEAR_Error')) {
            Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
            return $result;
        }

        /* Log the modification of this item in the history log. */
        $item = $this->get($itemId);
        if (!empty($item['uid'])) {
            $history = &Horde_History::singleton();
            $history->log('groo:' . $this->_collection . ':' . $item['uid'], array('action' => 'modify'), true);
        }

        return true;
    }

    /**
     * Move a item to a new collection.
     *
     * @param string $itemId      The item to move.
     * @param string $newCollection  The new collection.
     */
    function move($itemId, $newCollection)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        $query = 'UPDATE ' . $this->_params['table_prefix'].'items' .
                 ' SET item_owner = ?' .
                 ' WHERE item_owner = ? AND item_id = ?';
        $values = array($newCollection, $this->_collection, $itemId);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::move(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Attempt the move query. */
        $result = $this->_db->query($query, $values);
        if (is_a($result, 'PEAR_Error')) {
            Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
            return $result;
        }

        return true;
    }

    function delete($itemId)
    {
        $this->_connect();

        /* Get the item's details for use later. */
        $item = $this->get($itemId);

        $query = 'DELETE FROM ' . $this->_params['table_prefix'].'items' .
                 ' WHERE item_owner = ? AND item_id = ?';
        $values = array($this->_collection, $itemId);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::delete(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Attempt the delete query. */
        $result = $this->_db->query($query, $values);

        if (is_a($result, 'PEAR_Error')) {
            Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
            return $result;
        }

        /* Log the deletion of this item in the history log. */
        if (!empty($item['uid'])) {
            $history = &Horde_History::singleton();
            $history->log('groo:' . $this->_collection . ':' . $item['uid'], array('action' => 'delete'), true);
        }

        return true;
    }

    function deleteAll()
    {
        $this->_connect();

        $query = 'DELETE FROM ' . $this->_params['table_prefix'].'items' .
                 ' WHERE item_owner = ?';
        $values = array($this->_collection);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::deleteAll(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Attempt the delete query. */
        $result = $this->_db->query($query, $values);

        return is_a($result, 'PEAR_Error') ? $result : true;
    }

    /**
     * Retrieves all of the items from $this->_collection from the
     * database.
     *
     * @return mixed  True on success, PEAR_Error on failure.
     */
    function retrieve()
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        /* Build the SQL query. */
        $query = 'SELECT * FROM ' . $this->_params['table_prefix'].'items' .
                 ' WHERE item_owner = ?';
        $values = array($this->_collection);

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::retrieve(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Execute the query. */
        $result = $this->_db->query($query, $values);

        if (!is_a($result, 'PEAR_Error')) {
            $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
            if (is_a($row, 'PEAR_Error')) {
                return $row;
            }

            /* Store the retrieved values in a fresh $items list. */
            $this->_items = array();
            while ($row && !is_a($row, 'PEAR_Error')) {
                /* Add this new item to the $items list. */
                $this->_items[$row['item_id']] = $this->_buildItem($row);

                /* Advance to the new row in the result set. */
                $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
            }
            $result->free();
        } else {
            return $result;
        }

        return true;
    }

    /**
     * Attempts to open a persistent connection to the SQL server.
     *
     * @return boolean  True on success; exits (Horde::fatal()) on error.
     */
    function _connect()
    {
        if (!$this->_connected) {
            Horde::assertDriverConfig($this->_params, 'storage',
                array('phptype', 'charset'));

            if (!isset($this->_params['database'])) {
                $this->_params['database'] = '';
            }
            if (!isset($this->_params['username'])) {
                $this->_params['username'] = '';
            }
            if (!isset($this->_params['hostspec'])) {
                $this->_params['hostspec'] = '';
            }

            if (!isset($this->_params['table_prefix'])) {
                $this->_params['table_prefix'] = 'groo_';
            }

            /* Connect to the SQL server using the supplied parameters. */
            require_once 'DB.php';
            $this->_db = &DB::connect($this->_params,
                                      array('persistent' => !empty($this->_params['persistent'])));
            if (is_a($this->_db, 'PEAR_Error')) {
                Horde::fatal($this->_db, __FILE__, __LINE__);
            }

            // Set DB portability options.
            switch ($this->_db->phptype) {
            case 'mssql':
                $this->_db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS | DB_PORTABILITY_RTRIM);
                break;
            default:
                $this->_db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS);
            }

            $this->_connected = true;
        }

        return true;
    }

    function _buildItem($row)
    {
        /* Make sure items always have a UID. */
        if (empty($row['item_uid'])) {
            $row['item_uid'] = $this->generateUID();

            $query = 'UPDATE ' . $this->_params['table_prefix'].'items' .
                ' SET item_uid = ?' .
                ' WHERE item_owner = ? AND item_id = ?';
            $values = array($row['item_uid'], $row['item_owner'], $row['item_id']);

            /* Log the query at a DEBUG log level. */
            Horde::logMessage(sprintf('Groo_Driver_sql adding missing UID: %s', $query),
                              __FILE__, __LINE__, PEAR_LOG_DEBUG);
            $this->_db->query($query, $values);
        }

        /* Create a new item based on $row's values. */
        return array('itemlist_id' => $row['item_owner'],
                     'item_id' => $row['item_id'],
                     'uid' => String::convertCharset($row['item_uid'], $this->_params['charset']),
                     'desc' => String::convertCharset($row['item_desc'], $this->_params['charset']),
                     'book_id' => $row['item_book_id'],
                     'book' => $this->getBook($row['item_book_id']),
                     'body' => String::convertCharset($row['item_body'], $this->_params['charset']),
                     'modified' => substr($row['item_modified'],0,16), //0000-00-00 00:00:00
                     'category' => String::convertCharset($row['item_category'], $this->_params['charset']));
    }
    
    /**
     * Add a book to the backend storage.
     *
     * @param string $isbn13   The ISBN-13 of the book.
     *
     * @return int  The ID of the new book.
     */
    function addBookISBN13($isbn13, $owner)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        ///FIXME: configuration 'tableprefix' is not being found, but is there!
        ///$table = $this->_params['tableprefix'].'_books';
        $table = 'groo_books';
        $id = $this->_db->nextId($table);

        $query = sprintf(
            'INSERT INTO %s (book_id, isbn13, metadata_source, created_dt) ' .
            'VALUES (%d, %s, %s, NOW())',
            $table,
            $id,
            $this->_db->quote($isbn13),
            $this->_db->quote($owner)
        );

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::add(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Attempt the insertion query. */
        $result = $this->_db->query($query);

        /* Return an error immediately if the query failed. */
        if (is_a($result, 'PEAR_Error')) {
            Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
            return $result;
        }
        
        return $id;
    }
    
    /**
     * Retrieves a book given the bookId or the isbn13,
     * or retrieves all books if bookId and isbn13 are NULL
     *
     * @return Book object or array of Book objects on success|PEAR_Error on failure.
     */
    function getBook($bookId, $isbn13=NULL)
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        /* Build the SQL query. */
        $tBooks = $this->_params['table_prefix'].'books';
        $tCreators = $this->_params['table_prefix'].'creators';
        $tLink = $this->_params['table_prefix'].'l_book_creator';
        
        if (is_null($bookId)) {
            if (is_null($isbn13)) {
                $idClause = '';
            } else {
                $idClause = 'WHERE bk.isbn13 = '.$this->_db->quote($isbn13);
            }
        } else {
            $idClause = 'WHERE bk.book_id = '.(int)$bookId;
        }
        
        $sql = 'SELECT '
                . ' bk.book_id , bk.isbn13 , bk.title , '
                . ' bk.edition , bk.publisher , bk.issued , '
                . ' lbc.citation_order , cr.name , lbc.role '
            . ' FROM '
                . "$tBooks AS bk"
            . ' LEFT JOIN ( '
                    . "$tCreators AS cr , "
                    . "$tLink AS lbc "
                .') ON ('
                . ' bk.book_id = lbc. book_id'
                . ' AND cr.creator_id = lbc.creator_id '
            . ' ) '
            . $idClause
            . ' ORDER BY '
                . ' bk.title_sort , lbc.citation_order LIMIT 0, 100 ';
        
        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::getBook(): %s', $sql),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        $result = $this->_db->query($sql);

        if (is_a($result, 'PEAR_Error')) {
            return $result;
        }

        $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
        if (is_a($row, 'PEAR_Error')) {
            return $row;
        }
        if (is_null($row)) {
            return NULL;    
        }

        $books = array();
        $currentId = NULL;
        while ($row && !is_a($row, 'PEAR_Error')) {
            /* Add this new item to the $items list. */
            if ($currentId!=$row['book_id']) { // new book record
                if (!is_null($currentId)) {
                    $books[] = new Book($book);    
                }
                #. bk.book_id, bk.isbn13, bk.title, bk.edition, bk.publisher, bk.issued
                $currentId = $row['book_id'];
                $book = array(
                    'recordId' => $currentId,
                    'isbn13' => $row['isbn13'],
                    'title' => String::convertCharset($row['title'], $this->_params['charset']),
                    'edition' => String::convertCharset($row['edition'], $this->_params['charset']),
                    'publisher' => String::convertCharset($row['publisher'], $this->_params['charset']),
                    'issued' => String::convertCharset($row['issued'], $this->_params['charset']),
                );
            }
            #. ' lbc.citation_order , cr.name , lbc.role '
            if (!is_null($row['name'])) {
                $name = trim(String::convertCharset($row['name'], $this->_params['charset']));
                if (!array_key_exists('creators', $book)) {
                    $book['creators'] = array();    
                }
                $book['creators'][] = array(
                    'name' => $name,
                    'role' => String::convertCharset($row['role'], $this->_params['charset'])
                );    
            }
            /* Advance to the new row in the result set. */
            $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
        }
        if (is_a($row, 'PEAR_Error')) {
            return $row;
        }
        $books[] = new Book($book);    
        $result->free();
        
        if ($idClause) {
            return $books[0];           
        } else {
            return $books;           
        }
        
    } // function getBook

    /**
     * Retrieves all books in the database
     *
     * @return array of Book objects on success|PEAR_Error on failure.
     */
    function getBooks() {
        return $this->getBook(NULL);
    } // function getBook

    /**
     * Retrieves a book given the ISBN-13
     *
     * @return Book object on success|PEAR_Error on failure.
     */
    function getBookISBN13($isbn13) {
        return $this->getBook(NULL,$isbn13);
    } // function getBook



    /**
     * Generate the title_sort field by removing a leading article
     * and appending it at the end of the title.
     * TODO: implement actual placement of the article.
     * It turns out there are quite a few difficult cases:
     * - The Bob Dylan Scrapbook, 1956-1966
     * - The Definitive Guide to MySQL 5, Third Edition (Definitive Guide)
     * - The Fugitive Game: Online with Kevin Mitnick
     * @return mixed  True on success, PEAR_Error on failure.
     */
    function createTitleSort()
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        /* Build the SQL query. */
        $query = 'SELECT book_id, title FROM ' . $this->_params['table_prefix'].'books'
            . ' ORDER BY title';

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Groo_Driver_sql::retrieve(): %s', $query),
                          __FILE__, __LINE__, PEAR_LOG_DEBUG);

        /* Execute the query. */
        $result = $this->_db->query($query);
        if (is_a($result, 'PEAR_Error')) {
            var_export($result); return;
        }    
        $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
        if (is_a($row, 'PEAR_Error')) {
            var_export($row); return;
        }    
        $sql = array();
        echo "<pre>";
        //TODO: implement i18n for this
        //currently these are English and Portuguese articles
        $articles = array('the','a','an', 'o', 'os', 'as');
        while ($row && !is_a($row, 'PEAR_Error')) {
            /* Add this new item to the $items list. */
            $title = trim($row['title']);
            if ($title) {
                $words = preg_split("/\s+/",$title);
                if (in_array(strtolower($words[0]),$articles)) {
                    $article = $words[0];
                    unset($words[0]);
                } else {
                    $article = '';    
                }
                $title_sort = implode(' ',$words);
                $list[$row['book_id']] = $title_sort;
                $query = 'UPDATE groo_books SET title_sort = '.$this->_db->quote($title_sort). 
                    ' WHERE book_id = '.$row['book_id'].";\n";
                echo $query;
                $upd = $this->_db->query($query);
                if (is_a($upd, 'PEAR_Error')) {
                    var_export($upd); return;
                }    
                //echo $row['book_id']."\t".$article."\t".$title_sort."\n";
            }
            $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
        }
        echo "</pre>";
    }

}
